package com.fragrance.util;



import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil {
	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	  public  static List<List<String[]>> readExcel_xlsx(InputStream input) throws IOException{
    	  try {
    		  Workbook wb = WorkbookFactory.create(input);
			  if(wb==null){
				return null;
			  }
			  ArrayList<List<String[]>> ls = new  ArrayList<List<String[]>>(); 
			  int sheet_numbers = wb.getNumberOfSheets();
			  String[] sheetnames=new String[sheet_numbers];  
			  DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();   
			  for(int i=0;i<sheet_numbers;i++){//�������б�               
			        ArrayList<String[]> ls_a = new  ArrayList<String[]>(); 
			        Sheet sheet = wb.getSheetAt(i);  
			        sheetnames[i] = sheet.getSheetName();
			        for( int rows=1;rows<=sheet.getLastRowNum();rows++){
			        	Row row = sheet.getRow(rows);//    
			        	if(null != row && !isRowNull(row)){
			        		String[] s = new String[row.getLastCellNum()+1];//
			        		for( int columns=0;columns<row.getLastCellNum();columns++){
			        			Cell  cell = row.getCell(columns);                    
			        			if(cell!=null){   
			        				switch (cell.getCellType()) {    
			        				case XSSFCell.CELL_TYPE_STRING: // 
			        					s[columns] = cell.getStringCellValue();    
			        					if(s[columns]==null){  
			        						s[columns]="";  
			        					}  
			        					break;   
			        				case XSSFCell.CELL_TYPE_NUMERIC: //             
			        					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
			        					if (HSSFDateUtil.isCellDateFormatted(cell)) {  
			        						Date date = cell.getDateCellValue();  
			        						s[columns] = sdf.format(date);  
			        					} else if (cell.getCellStyle().getDataFormat() == 58) {  
			        						// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
			        						double value = cell.getNumericCellValue();  
			        						Date date = org.apache.poi.ss.usermodel.DateUtil  
			        								.getJavaDate(value);  
			        						s[columns] = sdf.format(date);  
			        					} else {  
			        						double value = cell.getNumericCellValue();  
			        						CellStyle style = cell.getCellStyle();  
			        						DecimalFormat format = new DecimalFormat();  
			        						String temp = style.getDataFormatString();  
			        						// 单元格设置成常规  
			        						if (temp.equals("General")) {  
			        							format.applyPattern("#");  
			        						}  
			        						s[columns] = format.format(value);  
			        					}  
			        					break;  
			        				case XSSFCell.CELL_TYPE_BLANK: //  
			        					s[columns]="";    
			        					break;     
			        				default:     
			        					System.out.print("");     
			        					break;     
			        				}    
			        			}  

			        		}  
			        		s[s.length-1] = rows+1+"";
			        		ls_a.add(s);
			        		logger.debug("excle解析第"+rows+"行的数据为"+Arrays.toString(s));
			        	}  
			        }
			    ls.add(ls_a);     
			    input.close();
			  }
			  return  ls;
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(input!=null){
				input.close();
			}
		}
		return null;
    }
	
	    public  static List<List<String[]>> readExcel(InputStream input) throws IOException{
	    	  try {
				  //XSSFWorkbook wb =  new XSSFWorkbook(new BufferedInputStream(input));   
				  HSSFWorkbook wb =  new HSSFWorkbook(new BufferedInputStream(input));
				  ArrayList<List<String[]>> ls = new  ArrayList<List<String[]>>(); 
				  int sheet_numbers = wb.getNumberOfSheets();
				  String[] sheetnames=new String[sheet_numbers];  
				  DecimalFormat df = (DecimalFormat) NumberFormat.getPercentInstance();   
				  for(int i=0;i<sheet_numbers;i++){       
				        ArrayList<String[]> ls_a = new  ArrayList<String[]>(); 
				        //XSSFSheet sheet = wb.getSheetAt(i);  
				        HSSFSheet sheet = wb.getSheetAt(i); 
				        sheetnames[i] = sheet.getSheetName();
				        for( int rows=1;rows<=sheet.getLastRowNum();rows++){
				            //XSSFRow row = sheet.getRow(rows); 
				            HSSFRow row = sheet.getRow(rows); 
				            String[] s = new String[row.getLastCellNum()+1];//
				            for( int columns=0;columns<row.getLastCellNum();columns++){//
				                //s[columns] = row.getCell(columns).getStringCellValue(); 
				                //XSSFCell  cell = row.getCell(columns);  
				                HSSFCell  cell = row.getCell(columns);  
				                if(cell!=null){   
					                switch (cell.getCellType()) {    
					                     case HSSFCell.CELL_TYPE_STRING: // 
					                         s[columns] = cell.getStringCellValue();    
					                         if(s[columns]==null){  
					                             s[columns]="";  
					                         }  
					                         break;   
					                     case HSSFCell.CELL_TYPE_NUMERIC: //             
					                    	 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
					                    	 if (HSSFDateUtil.isCellDateFormatted(cell)) {  
					                             Date date = cell.getDateCellValue();  
					                             s[columns] = sdf.format(date);  
					                         } else if (cell.getCellStyle().getDataFormat() == 58) {  
					                             // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
					                             double value = cell.getNumericCellValue();  
					                             Date date = org.apache.poi.ss.usermodel.DateUtil  
					                                     .getJavaDate(value);  
					                             s[columns] = sdf.format(date);  
					                         } else {  
					                             double value = cell.getNumericCellValue();  
					                             CellStyle style = cell.getCellStyle();  
					                             DecimalFormat format = new DecimalFormat();  
					                             String temp = style.getDataFormatString();  
					                             // 单元格设置成常规  
					                             if (temp.equals("General")) {  
					                                 format.applyPattern("#");  
					                             }  
					                             s[columns] = format.format(value);  
					                         }  
					                         break;  
					                     case HSSFCell.CELL_TYPE_BLANK: //  
					                         s[columns]="";    
					                         break;     
					                     default:     
					                         System.out.print("");     
					                         break;     
					                     }    
				               }  
				                
				            }  
				            s[s.length-1] = rows+1+"";
				            ls_a.add(s);
				    }  
				    ls.add(ls_a);     
				    input.close();
				  }
				  return  ls;
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				if(input!=null){
					input.close();
				}
			}
			return null;
	    }
	    
	    @SuppressWarnings("deprecation")
		public static HSSFWorkbook createExcelSheet(HSSFWorkbook wb,Map<String,Object> excelMap, List<Map<String,String>> dataList){
   	     String sheetName = excelMap.get("sheetName")==null?"sheet1":(String)excelMap.get("sheetName");
   	     if(wb==null){
   	    	 wb = new HSSFWorkbook();  
   	     }
   	     HSSFSheet sheet = wb.createSheet(sheetName);  //各连锁每日/周/月/季度   问诊数据：
   	     sheet.setDefaultColumnWidth(20);
            HSSFRow row = sheet.createRow((int) 0);  
            
            HSSFFont font=wb.createFont();
            font.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
            font.setFontHeightInPoints((short)12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗
            
            HSSFFont font_14 = wb.createFont();
            font_14.setColor(HSSFColor.BLACK.index);//HSSFColor.VIOLET.index //字体颜色
            font_14.setFontHeightInPoints((short)14);
            font_14.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         //字体增粗
            
            // 第四步，创建单元格，并设置值表头 设置表头居中  
            HSSFCellStyle style = wb.createCellStyle();  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
            
            HSSFCellStyle headStyle = wb.createCellStyle();  
            headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 表头style
            headStyle.setFont(font);
            
            
            HSSFCellStyle row0Style = wb.createCellStyle();  
            //row0Style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 第一行style
            row0Style.setFont(font_14);
            
            sheet.setDefaultColumnWidth(20);

            HSSFRow row1_ = sheet.createRow((int) 0);  
            HSSFCell cell_ = null;
            List<String> colums = (List<String>)excelMap.get("colums");
            for(int i = 0;i<colums.size(); i++){
           	 cell_ =  row1_.createCell((short) i);
           	 cell_.setCellValue(colums.get(i));
           	 cell_.setCellStyle(headStyle);
           	 
            }
            
            Map<String,String> dataMap = null;
            for(int j =0; j<dataList.size();j++){
           	 row = sheet.createRow((int)j+1);
           	 dataMap = dataList.get(j);
           	 HSSFCell dataCell = null;
           	 for(int i = 0;i<colums.size(); i++){
           		 dataCell = row.createCell((short) i);
           		 dataCell.setCellValue(dataMap.get(i+"")==null?"":dataMap.get(i+""));
           		 dataCell.setCellStyle(style);
           	 }
            }
   	return wb;
   }   
	    
	/**
	 * 检查当前行是否为空
	 * @param row
	 * @return  是 返回true  否 false
	 */
    private static boolean isRowNull(Row row){
    	boolean flag = false;
    	for (int i = 0; i < row.getLastCellNum(); i++) {
    		Cell cell = row.getCell(i);
    		if(cell == null || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK){
    			flag = true;
    		}else{
    			flag = false;
    		}
    	}
    	return flag;
    }
}
